﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace Spore.SQL
{
    public class SqlStatement
    {

        private WhereExpressionBuilder weBuilder;

        public Dictionary<string, object> Args { get; private set; }

        public SqlStatement(string sqlhead,
            string expression,
            string sort = "")
        {
            this.Args = new Dictionary<string, object>();
            this.HeadPart = sqlhead;
            this.SortPart = sort;
            this.Expression = expression;

            this.weBuilder = WhereExpressionBuilder.New(this.Expression);
        }

        /// <summary>
        /// sql语句的条件部分 不包含where
        /// </summary>
        public string HeadPart { get; private set; }

        /// <summary>
        /// 计数的头部
        /// </summary>
        public string CountHeadPart
        {
            get
            {
                int index = this.HeadPart.IndexOf("from", StringComparison.OrdinalIgnoreCase);
                return "select count(*) " + this.HeadPart.Substring(index);
            }
        }

        /// <summary>
        /// sql语句的条件部分
        /// </summary>
        public string WherePart
        {
            get
            {
                return this.weBuilder.Process();
            }
        }

        /// <summary>
        /// sql语句的排序部分
        /// </summary>
        public string SortPart { get; private set; }

        /// <summary>
        /// 条件部分的组装模版
        /// </summary>
        public string Expression { get; private set; }

        /// <summary>
        /// 设置expression部分的where子句
        /// </summary>
        /// <param name="expKey"></param>
        /// <param name="clause"></param>
        /// <param name="argkey"></param>
        /// <param name="arg"></param>
        public void SetWhere(string expKey, string clause, string argkey = "", object arg = null)
        {
            this.weBuilder.Data[expKey] = clause;
            if (!string.IsNullOrWhiteSpace(argkey))
            {
                this.Args.Add(argkey, arg);
            }
        }

        /// <summary>
        /// 获取SqlParameter数组
        /// </summary>
        /// <returns></returns>
        public SqlParameter[] GetSqlParameters()
        {
            List<SqlParameter> sps = new List<SqlParameter>();
            foreach (var kvp in Args)
            {
                sps.Add(new SqlParameter(kvp.Key, kvp.Value));
            }
            return sps.ToArray();
        }

        /// <summary>
        /// 正常sql
        /// </summary>
        /// <returns></returns>
        public string ToSql()
        {
            string wherepart = this.WherePart;
            wherepart = string.IsNullOrWhiteSpace(wherepart) ? "" : " where " + wherepart;
            return string.Format("{0} \r\n {1} \r\n {2} ", this.HeadPart, wherepart, this.SortPart);
        }

        public DbCommand ToSqlCmd(Database database)
        {
            var cmd = database.GetSqlStringCommand(this.ToSql());
            foreach (var kvp in Args)
            {
                cmd.Parameters.Add(new SqlParameter(kvp.Key, kvp.Value));
            }
            return cmd;
        }

        /// <summary>
        /// 统计总数的sql
        /// </summary>
        /// <returns></returns>
        public string ToCountSql()
        {
            string wherepart = this.WherePart;
            wherepart = string.IsNullOrWhiteSpace(wherepart) ? "" : " where " + wherepart;
            return string.Format("{0} \r\n {1} ", this.CountHeadPart, wherepart);
        }

        /// <summary>
        /// 获取统计数量的cmd 适用于 sqlserver
        /// </summary>
        /// <param name="database"></param>
        /// <returns></returns>
        public DbCommand ToCountSqlCmd(Database database)
        {
            var cmd = database.GetSqlStringCommand(this.ToCountSql());
            foreach (var kvp in Args)
            {
                cmd.Parameters.Add(new SqlParameter(kvp.Key, kvp.Value));
            }
            return cmd;
        }

        /// <summary>
        /// 分页sql
        /// </summary>
        /// <param name="totalCount"></param>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public string ToPagerSql(int totalCount, int page, int pagesize)
        {
            if (page <= 0 || pagesize <= 0)//获取全部数据
            {
                return ToSql();
            }
            else
            {
                return Tools.GeneratePagerSQL4MSSQL(this.ToSql(), "", pagesize, page, totalCount);
            }
        }

        /// <summary>
        /// 获取分页的cmd 适用于 sqlserver
        /// </summary>
        /// <param name="database"></param>
        /// <returns></returns>
        public DbCommand ToPagerSqlCmd(Database database, int totalCount, int page, int pagesize)
        {
            var cmd = database.GetSqlStringCommand(this.ToPagerSql(totalCount, page, pagesize));
            foreach (var kvp in Args)
            {
                cmd.Parameters.Add(new SqlParameter(kvp.Key, kvp.Value));
            }
            return cmd;
        }

    }
}
